package com.wy.utils;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @描述
 * @创建人 wangyue
 * @创建时间2019/9/1110:05
 */

public class MyExcelUtils {

    public static void main (String[] args) throws Exception{
        MyExcelUtils.importExcel();
    }
    //导出
    public static void export() throws Exception{
        //创建工作薄；
        XSSFWorkbook xw=new XSSFWorkbook();
        //根据工作薄创建sheet
        XSSFSheet sheet = xw.createSheet("信息列表");
        //创建表头
        XSSFRow row = sheet.createRow(0);
        XSSFCell cell = row.createCell(0);
        cell.setCellValue("姓名");

        XSSFCell cell1 = row.createCell(1);
        cell1.setCellValue("邮箱");

        XSSFCell cell2 = row.createCell(2);
        cell2.setCellValue("电话");

        for(int i=0;i<10;i++){
            XSSFRow row1 = sheet.createRow(i+1);
            XSSFCell cel1l = row1.createCell(0);
            cel1l.setCellValue("姓名"+i);

            XSSFCell cell11 = row1.createCell(1);
            cell11.setCellValue("邮箱"+i);

            XSSFCell cell12 = row1.createCell(2);
            cell12.setCellValue("电话"+i);
        }
        //添加数据
        FileOutputStream os = new FileOutputStream("D:\\2018\\123.xlsx");
        //把工作薄输出成excel文件；
        xw.write(os);
    }

    //导入
    public static void importExcel() throws Exception{
        //上传excel
        //把excel流转换为workbook；
        File file = new File("D:\\2018\\123.xlsx");
        if(!file.exists()){
            System.out.println("文件不存在");
        }else{
            FileInputStream is = new FileInputStream(file);
            XSSFWorkbook wb = new XSSFWorkbook(is);
            //读取workbook里边的sheet
            XSSFSheet sheet = wb.getSheetAt(0);
            //读取sheet里边的行；
            int rowNum = sheet.getLastRowNum()+1;//获取总行数
            // 根据第一行获取列数
            Row row = sheet.getRow(0);
            //获取总列数
            int colNum = row.getPhysicalNumberOfCells();
            List<User> userList = new ArrayList<User>();
            for(int i=1;i<rowNum;i++){
                XSSFRow row1 = sheet.getRow(i);
                User user = new User();
                String name = row1.getCell(0).getStringCellValue();
                if(name == null){
                    name ="";
                }
                user.setName(name);
                String phone = row1.getCell(1).getStringCellValue();
                if(phone == null){
                    phone ="";
                }
                user.setPhone(phone);
                String email = row1.getCell(2).getStringCellValue();
                if(email == null){
                    email ="";
                }
                user.setEmail(email);
                userList.add(user);
            }
            for(User user:userList){
                System.out.println(user.toString());
            }
        }
    }
}
